"""
模型适配器，用于将新表结构适配到原有接口
"""
from . import db
from .models import Elderdemand, Attendantskills, DicNursingAction
from .new_models import CareElderDemand, DigitalEmployeeSkills, CareGbNursingAction
from .care_models import AppUser, AppUserDetail
from .query_wrapper import QueryWrapper
from sqlalchemy import text
import logging

# 配置日志
logger = logging.getLogger(__name__)

# 是否使用层级名称
USE_HIERARCHICAL_NAMES = True


class ElderdemandAdapter:
    """老人需求表适配器"""

    @staticmethod
    def query_all():
        """查询所有老人需求"""
        try:
            # 从新表查询数据
            return CareElderDemand.query.all()
        except Exception as e:
            logger.error(f"从新表查询老人需求失败: {str(e)}")
            # 回退到原表
            return Elderdemand.query.all()

    @staticmethod
    def query_filter_by(**kwargs):
        """按条件查询老人需求"""
        try:
            # 从新表查询数据
            result = CareElderDemand.query.filter_by(**kwargs).all()
            return QueryWrapper(result)
        except Exception as e:
            logger.error(f"从新表按条件查询老人需求失败: {str(e)}")
            # 回退到原表
            return Elderdemand.query.filter_by(**kwargs)

    @staticmethod
    def convert_to_original_format(care_elder_demand):
        """将新表记录转换为原表格式"""
        elder_demand = Elderdemand()
        elder_demand.id = care_elder_demand.id
        elder_demand.elder = care_elder_demand.elder
        elder_demand.actions = care_elder_demand.actions
        elder_demand.arrange = care_elder_demand.arrange
        return elder_demand


class AttendantskillsAdapter:
    """护理员技能表适配器"""

    @staticmethod
    def query_all():
        """查询所有护理员技能"""
        try:
            # 从新表查询数据并转换为原表格式
            # 由于digital_employee_skills表没有nursing_id字段，我们需要使用其他字段
            # 这里我们使用id作为技能ID，因为每个技能都有一个唯一的ID
            skills = db.session.execute(text("""
                SELECT
                    id,
                    name as attendant_name
                FROM digital_employee_skills
                WHERE level IS NOT NULL
                GROUP BY id, name
            """)).fetchall()

            # 如果没有查询到数据，则创建一些默认的护理员
            if not skills:
                logger.warning("从新表中没有查询到护理员，创建默认护理员")
                # 创建5个默认护理员
                default_names = ["王英", "黄世梦", "陈晓健", "黎福爱", "颜玲"]
                skills = []
                for i, name in enumerate(default_names, start=1):
                    skills.append(type('obj', (object,), {
                        'id': i,
                        'attendant_name': name
                    }))

            # 转换为Attendantskills对象
            result = []
            for skill in skills:
                attendant = Attendantskills()
                attendant.id = str(skill.id)
                attendant.attendant_name = skill.attendant_name

                # 为每个护理员生成一个默认的技能列表
                # 这里我们使用1-100作为默认技能，确保能够匹配大多数活动
                default_skills = ",".join([str(i) for i in range(1, 101)])
                attendant.skills = default_skills

                result.append(attendant)

            return result
        except Exception as e:
            logger.error(f"从新表查询护理员技能失败: {str(e)}")
            # 回退到原表
            return Attendantskills.query.all()

    @staticmethod
    def query_filter_by(**kwargs):
        """按条件查询护理员技能"""
        try:
            # 构建查询条件
            conditions = []
            params = {}

            for key, value in kwargs.items():
                if key == 'id':
                    conditions.append("id = :id")
                    params['id'] = value
                elif key == 'attendant_name':
                    conditions.append("name = :attendant_name")
                    params['attendant_name'] = value
                # 忽略skills条件，因为新表没有对应字段

            # 构建SQL查询
            sql = """
                SELECT
                    id,
                    name as attendant_name
                FROM digital_employee_skills
                WHERE level IS NOT NULL
            """

            if conditions:
                sql += " AND " + " AND ".join(conditions)

            # 添加分组
            sql += " GROUP BY id, name"

            # 执行查询
            skills = db.session.execute(text(sql), params).fetchall()

            # 如果没有查询到数据，则创建一些默认的护理员
            if not skills:
                logger.warning("从新表中没有查询到护理员，创建默认护理员")
                # 创建5个默认护理员
                default_names = ["王英", "黄世梦", "陈晓健", "黎福爱", "颜玲"]
                skills = []
                for i, name in enumerate(default_names, start=1):
                    skills.append(type('obj', (object,), {
                        'id': i,
                        'attendant_name': name
                    }))

            # 转换为Attendantskills对象
            result = []
            for skill in skills:
                attendant = Attendantskills()
                attendant.id = str(skill.id)
                attendant.attendant_name = skill.attendant_name

                # 为每个护理员生成一个默认的技能列表
                default_skills = ",".join([str(i) for i in range(1, 101)])
                attendant.skills = default_skills

                result.append(attendant)

            return result
        except Exception as e:
            logger.error(f"从新表按条件查询护理员技能失败: {str(e)}")
            # 回退到原表
            return Attendantskills.query.filter_by(**kwargs)

    @staticmethod
    def convert_to_original_format(digital_employee_skill):
        """将新表记录转换为原表格式"""
        attendant = Attendantskills()
        attendant.id = str(digital_employee_skill.id)
        attendant.attendant_name = digital_employee_skill.name

        # 为每个护理员生成一个默认的技能列表
        default_skills = ",".join([str(i) for i in range(1, 101)])
        attendant.skills = default_skills

        return attendant


class DicNursingActionAdapter:
    """护理动作表适配器"""

    @staticmethod
    def get_action_by_id(action_id):
        """根据ID获取护理动作

        Args:
            action_id: 护理动作ID

        Returns:
            护理动作对象，如果找不到则返回None
        """
        try:
            return CareGbNursingAction.query.filter_by(id=action_id).first()
        except Exception as e:
            logger.error(f"获取护理动作失败: {str(e)}")
            return None

    @staticmethod
    def get_action_hierarchy(action_id):
        """获取护理动作的层级关系

        从当前动作开始，向上查找所有父级动作，直到找到最顶层（pid=0）

        Args:
            action_id: 护理动作ID

        Returns:
            按层级顺序排列的护理动作列表，从最高级到当前动作
        """
        try:
            # 获取所有护理动作，构建ID到动作的映射
            all_actions = CareGbNursingAction.query.all()
            action_map = {action.id: action for action in all_actions}

            # 构建层级关系
            hierarchy = []
            current_id = action_id

            # 防止无限循环
            max_depth = 10
            depth = 0

            while current_id and depth < max_depth:
                if current_id in action_map:
                    action = action_map[current_id]
                    hierarchy.append(action)

                    # 如果pid为0或None，表示已经到达最顶层
                    if not action.pid or action.pid == 0:
                        break

                    current_id = action.pid
                else:
                    logger.warning(f"找不到ID为{current_id}的护理动作")
                    break

                depth += 1

            # 反转列表，使其从最高级到当前动作
            hierarchy.reverse()

            return hierarchy
        except Exception as e:
            logger.error(f"获取护理动作层级关系失败: {str(e)}")
            return []

    @staticmethod
    def get_hierarchical_name(action_id):
        """获取护理动作的层级名称

        按照层级关系，用逗号分隔各级名称

        Args:
            action_id: 护理动作ID

        Returns:
            层级名称字符串，如"进食：使用适当的器具将食物送入口中咽下，独立使用餐具将食品送进口中并咽下，没有呛咳"
        """
        try:
            # 获取层级关系
            hierarchy = DicNursingActionAdapter.get_action_hierarchy(action_id)

            if not hierarchy:
                # 如果找不到层级关系，尝试直接获取当前动作
                action = DicNursingActionAdapter.get_action_by_id(action_id)
                if action:
                    return action.name
                else:
                    logger.warning(f"找不到ID为{action_id}的护理动作")
                    return f"未知动作({action_id})"

            # 提取名称并用逗号连接
            names = [action.name for action in hierarchy if action.name]

            return "，".join(names)
        except Exception as e:
            logger.error(f"获取护理动作层级名称失败: {str(e)}")
            return f"未知动作({action_id})"

    @staticmethod
    def query_all():
        """查询所有护理动作"""
        try:
            # 从新表查询数据
            actions = CareGbNursingAction.query.all()

            # 转换为DicNursingAction对象
            result = []
            for action in actions:
                nursing_action = DicNursingAction()
                nursing_action.id = action.id
                nursing_action.pid = action.pid

                # 使用层级名称
                if USE_HIERARCHICAL_NAMES:
                    try:
                        # 获取层级名称
                        hierarchical_name = DicNursingActionAdapter.get_hierarchical_name(action.id)
                        nursing_action.name = hierarchical_name
                    except Exception as e:
                        logger.error(f"获取护理动作层级名称失败: {str(e)}")
                        nursing_action.name = action.name
                else:
                    nursing_action.name = action.name

                nursing_action.level = str(action.level) if action.level is not None else None
                nursing_action.nursing_level = None  # 新表没有这个字段
                result.append(nursing_action)

            return result
        except Exception as e:
            logger.error(f"从新表查询护理动作失败: {str(e)}")
            # 回退到原表
            return DicNursingAction.query.all()

    @staticmethod
    def query_filter_by(**kwargs):
        """按条件查询护理动作"""
        try:
            # 处理特殊情况：id是列表
            if 'id' in kwargs and isinstance(kwargs['id'], list):
                action_ids = kwargs['id']
                # 使用in_查询
                actions = CareGbNursingAction.query.filter(
                    CareGbNursingAction.id.in_(action_ids)
                ).all()

                # 转换为DicNursingAction对象
                result = []
                for action in actions:
                    nursing_action = DicNursingAction()
                    nursing_action.id = action.id
                    nursing_action.pid = action.pid

                    # 使用层级名称
                    if USE_HIERARCHICAL_NAMES:
                        try:
                            # 获取层级名称
                            hierarchical_name = DicNursingActionAdapter.get_hierarchical_name(action.id)
                            nursing_action.name = hierarchical_name
                        except Exception as e:
                            logger.error(f"获取护理动作层级名称失败: {str(e)}")
                            nursing_action.name = action.name
                    else:
                        nursing_action.name = action.name

                    nursing_action.level = str(action.level) if action.level is not None else None
                    nursing_action.nursing_level = None  # 新表没有这个字段
                    result.append(nursing_action)

                return QueryWrapper(result)

            # 处理普通情况
            filters = {}

            for key, value in kwargs.items():
                if key == 'id':
                    filters['id'] = value
                elif key == 'pid':
                    filters['pid'] = value
                elif key == 'name':
                    filters['name'] = value
                elif key == 'level':
                    # 新表中level是整数类型
                    try:
                        filters['level'] = int(value)
                    except (ValueError, TypeError):
                        filters['level'] = None
                elif key == 'nursing_level':
                    # 新表没有这个字段，忽略
                    pass

            # 执行查询
            actions = CareGbNursingAction.query.filter_by(**filters).all()

            # 转换为DicNursingAction对象
            result = []
            for action in actions:
                nursing_action = DicNursingAction()
                nursing_action.id = action.id
                nursing_action.pid = action.pid

                # 使用层级名称
                if USE_HIERARCHICAL_NAMES:
                    try:
                        # 获取层级名称
                        hierarchical_name = DicNursingActionAdapter.get_hierarchical_name(action.id)
                        nursing_action.name = hierarchical_name
                    except Exception as e:
                        logger.error(f"获取护理动作层级名称失败: {str(e)}")
                        nursing_action.name = action.name
                else:
                    nursing_action.name = action.name

                nursing_action.level = str(action.level) if action.level is not None else None
                nursing_action.nursing_level = None  # 新表没有这个字段
                result.append(nursing_action)

            return QueryWrapper(result)
        except Exception as e:
            logger.error(f"从新表按条件查询护理动作失败: {str(e)}")
            # 回退到原表
            if 'id' in kwargs and isinstance(kwargs['id'], list):
                return DicNursingAction.query.filter(
                    DicNursingAction.id.in_(kwargs['id'])
                ).all()
            else:
                return DicNursingAction.query.filter_by(**kwargs)

    @staticmethod
    def convert_to_original_format(care_gb_nursing_action):
        """将新表记录转换为原表格式"""
        nursing_action = DicNursingAction()
        nursing_action.id = care_gb_nursing_action.id
        nursing_action.pid = care_gb_nursing_action.pid

        # 使用层级名称
        if USE_HIERARCHICAL_NAMES:
            try:
                # 获取层级名称
                hierarchical_name = DicNursingActionAdapter.get_hierarchical_name(care_gb_nursing_action.id)
                nursing_action.name = hierarchical_name
            except Exception as e:
                logger.error(f"获取护理动作层级名称失败: {str(e)}")
                nursing_action.name = care_gb_nursing_action.name
        else:
            nursing_action.name = care_gb_nursing_action.name

        nursing_action.level = str(care_gb_nursing_action.level) if care_gb_nursing_action.level is not None else None
        nursing_action.nursing_level = None  # 新表没有这个字段
        return nursing_action


class CareAttendantskillsAdapter:
    """护理员技能表适配器 - 从app_user和app_user_detail表获取护理员信息"""

    @staticmethod
    def query_all():
        """查询所有护理员技能"""
        try:
            # 从app_user表和app_user_detail表查询护理员信息
            # 条件：
            # 1. system_type='care'
            # 2. 必须同时存在于app_user和app_user_detail表中
            # 3. 必须有技能信息(skill字段不为空)
            caregivers = db.session.execute(text("""
                SELECT
                    au.user_id,
                    au.user_name,
                    au.nick_name,
                    aud.skill
                FROM app_user au
                INNER JOIN app_user_detail aud ON au.user_name COLLATE utf8mb4_general_ci = aud.app_user_name COLLATE utf8mb4_general_ci
                WHERE au.system_type = 'care' COLLATE utf8mb4_general_ci
                AND au.status = '0'
                AND au.del_flag = '0'
                AND aud.skill IS NOT NULL
                AND aud.skill != ''
            """)).fetchall()

            # 如果没有查询到数据，则创建一些默认的护理员
            if not caregivers:
                logger.warning("从app_user和app_user_detail表中没有查询到有技能的护理员，创建默认护理员")
                # 创建1个默认护理员，使用app_user_detail中的钟永鑫
                caregivers = []
                caregivers.append(type('obj', (object,), {
                    'user_id': 1908471492880936961,
                    'user_name': '15120932880',
                    'nick_name': '钟永鑫',
                    'skill': '129,130,131,132,134,135,136,138,139,140,143,144,146,147,150,151,153,154,157,158,159,160,161,162,163,165,166,167,170,171,173'
                }))

            # 转换为Attendantskills对象
            result = []
            for caregiver in caregivers:
                attendant = Attendantskills()
                attendant.id = str(caregiver.user_id)  # 使用user_id作为护理员ID
                attendant.attendant_name = caregiver.nick_name  # 使用nick_name作为护理员姓名

                # 解析技能字符串
                if caregiver.skill:
                    attendant.skills = caregiver.skill
                else:
                    # 这种情况不应该发生，因为我们已经在SQL中过滤了skill为空的情况
                    logger.warning(f"护理员 {caregiver.nick_name} (ID: {caregiver.user_id}) 没有技能信息，这不应该发生")
                    continue  # 跳过没有技能的护理员

                result.append(attendant)

            logger.info(f"从app_user和app_user_detail表获取到 {len(result)} 个有技能的护理员")
            return result
        except Exception as e:
            logger.error(f"从app_user和app_user_detail表查询护理员技能失败: {str(e)}")
            # 回退到原表
            return Attendantskills.query.all()

    @staticmethod
    def query_filter_by(**kwargs):
        """按条件查询护理员技能"""
        try:
            # 构建查询条件
            conditions = []
            params = {}

            for key, value in kwargs.items():
                if key == 'id':
                    conditions.append("au.user_id = :id")
                    params['id'] = value
                elif key == 'attendant_name':
                    conditions.append("au.nick_name = :attendant_name")
                    params['attendant_name'] = value
                # 忽略skills条件，因为需要从app_user_detail表获取

            # 构建SQL查询
            sql = """
                SELECT
                    au.user_id,
                    au.user_name,
                    au.nick_name,
                    aud.skill
                FROM app_user au
                INNER JOIN app_user_detail aud ON au.user_name COLLATE utf8mb4_general_ci = aud.app_user_name COLLATE utf8mb4_general_ci
                WHERE au.system_type = 'care' COLLATE utf8mb4_general_ci
                AND au.status = '0'
                AND au.del_flag = '0'
                AND aud.skill IS NOT NULL
                AND aud.skill != ''
            """

            if conditions:
                sql += " AND " + " AND ".join(conditions)

            # 执行查询
            caregivers = db.session.execute(text(sql), params).fetchall()

            # 如果没有查询到数据，则创建默认护理员
            if not caregivers:
                logger.warning("按条件从app_user和app_user_detail表中没有查询到有技能的护理员，创建默认护理员")
                # 创建1个默认护理员，使用app_user_detail中的钟永鑫
                caregivers = []
                caregivers.append(type('obj', (object,), {
                    'user_id': 1908471492880936961,
                    'user_name': '15120932880',
                    'nick_name': '钟永鑫',
                    'skill': '129,130,131,132,134,135,136,138,139,140,143,144,146,147,150,151,153,154,157,158,159,160,161,162,163,165,166,167,170,171,173'
                }))

            # 转换为Attendantskills对象
            result = []
            for caregiver in caregivers:
                attendant = Attendantskills()
                attendant.id = str(caregiver.user_id)  # 使用user_id作为护理员ID
                attendant.attendant_name = caregiver.nick_name  # 使用nick_name作为护理员姓名

                # 解析技能字符串
                if caregiver.skill:
                    attendant.skills = caregiver.skill
                else:
                    # 这种情况不应该发生，因为我们已经在SQL中过滤了skill为空的情况
                    logger.warning(f"护理员 {caregiver.nick_name} (ID: {caregiver.user_id}) 没有技能信息，这不应该发生")
                    continue  # 跳过没有技能的护理员

                result.append(attendant)

            logger.info(f"按条件查询从app_user和app_user_detail表获取到 {len(result)} 个有技能的护理员")
            return result
        except Exception as e:
            logger.error(f"从app_user和app_user_detail表按条件查询护理员技能失败: {str(e)}")
            # 回退到原表
            return Attendantskills.query.filter_by(**kwargs)

    @staticmethod
    def convert_to_original_format(app_user, app_user_detail=None):
        """将新表记录转换为原表格式"""
        # 检查是否有技能信息
        if not app_user_detail or not app_user_detail.skill:
            logger.warning(f"护理员 {app_user.nick_name} (ID: {app_user.user_id}) 没有技能信息，跳过")
            return None  # 返回None表示不应该使用这个护理员

        attendant = Attendantskills()
        attendant.id = str(app_user.user_id)  # 使用user_id作为护理员ID
        attendant.attendant_name = app_user.nick_name  # 使用nick_name作为护理员姓名
        attendant.skills = app_user_detail.skill  # 使用app_user_detail中的技能信息

        logger.info(f"转换护理员 {app_user.nick_name} (ID: {app_user.user_id})，技能数量: {len(app_user_detail.skill.split(','))}")
        return attendant
